package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Agency;
import ModelLayer.DiscountGroup;

public class DBAgency implements IFDBAgency {
	private Connection con;

	public DBAgency() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Agency getAgency(int agencyId, boolean retrieveAssociation) {
		String wClause = "  agencyId = '" + agencyId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertAgency(Agency agency) {
		int nextId = GetMaxId.getMaxId("Select max(agencyId) From agency") + 1;
		System.out.println("next agencyId = " + nextId);
		int rc = -1;
		Integer dgId = null;
		if (agency.getDiscountGroup() != null)
			dgId = agency.getDiscountGroup().getId();
		String query = "INSERT INTO agency(agencyId, name, phone, address, country, email, dg_Id) VALUES('"
				+ nextId
				+ "','"
				+ agency.getName()
				+ "','"
				+ agency.getPhone()
				+ "','"
				+ agency.getAddress()
				+ "','"
				+ agency.getCountry()
				+ "','" + agency.getEmail() + "'," + dgId + ")";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Customer is not inserted");
		}
		return rc;
	}

	@Override
	public ArrayList<Agency> getAllAgency(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public int updateAgency(Agency agency) {
		Agency agencyObj = agency;
		int rc = -1;
		Integer dgId = null;
		if (agency.getDiscountGroup() != null)
			dgId = agency.getDiscountGroup().getId();
		String query = "UPDATE agency SET " + "name = '" + agencyObj.getName()
				+ "'," + "phone = '" + agencyObj.getPhone() + "',"
				+ "address = '" + agencyObj.getAddress() + "'," + "country = '"
				+ agencyObj.getCountry() + "'," + "email = '"
				+ agencyObj.getEmail() + "'," + "dg_Id = " + dgId + ""
				+ " WHERE agencyId = '" + agencyObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			// TODO maybe make it throw exception
			System.out.println("Update exception in agency db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteAgency(int agencyId) {
		int rc = -1;

		String query = "DELETE FROM agency WHERE agencyId = '" + agencyId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in agency db: " + ex);
		}
		return (rc);
	}

	private Agency singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;

		Agency agency = new Agency();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				agency = buildAgency(results);
				stmt.close();

			}
		} catch (Exception e) {
			// TODO need to throw exception class
			System.out.println("Query exception: " + e);
		}
		return agency;
	}

	private ArrayList<Agency> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Agency> list = new ArrayList<Agency>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Agency agency = new Agency();
				agency = buildAgency(results);
				list.add(agency);
			}
			stmt.close();

		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Agency buildAgency(ResultSet results) {
		Agency agency = new Agency();
		try {
			agency.setId(results.getInt("agencyId"));
			agency.setName(results.getString("name"));
			agency.setPhone(results.getString("phone"));
			agency.setAddress(results.getString("address"));
			agency.setCountry(results.getString("country"));
			agency.setEmail(results.getString("email"));
			agency.setDiscountGroup(new DiscountGroup(results.getInt("dg_Id")));
		} catch (Exception e) {
			// TODO need to throw exception class
			System.out.println("error in building the agency object");
		}
		return agency;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT agencyId, name, phone, address, country, email, dg_Id FROM agency";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
